﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.Configuration;
using System.Data;

namespace iMall.AtoZ.Helper
{/// <summary>
    /// Access数据库操作助手
    /// </summary>
    public sealed class AccessDALHelper
    {
        /// <summary>
        /// 读取Access数据库的连接字符串
        /// 首先从缓存里读取，如果不存在则到配置文件中读取，并放入缓存
        /// </summary>
        /// <returns>Access数据库的连接字符串</returns>
        public static string GetConnectionString()
        {
            string dbPath = ConfigurationManager.AppSettings["AccessPath"];
            string dbAbsolutePath = HttpContext.Current.Server.MapPath(dbPath);
            string connectionString = ConfigurationManager.AppSettings["AccessConnectionString"];

            return string.Format(connectionString, dbAbsolutePath);
        }

        /// <summary>
        /// 执行SQL语句并且不返回任何值
        /// </summary>
        /// <param name="SQLCommand">所执行的SQL命令</param>
        /// <param name="parameters">参数集合</param>
        public static int ExecuteSQLNonQuery(string SQLCommand, OleDbParameter[] parameters)
        {
            using (OleDbConnection connection = new OleDbConnection(GetConnectionString()))
            {
                OleDbCommand command = new OleDbCommand(SQLCommand, connection);
                for (int i = 0; i < parameters.Length; i++)
                    command.Parameters.Add(parameters[i]);
                connection.Open();
                int re = command.ExecuteNonQuery();
                connection.Close();

                return re;
            }
        }

        /// <summary>
        /// 执行SQL语句并返回包含查询结果的DataSet
        /// </summary>
        /// <param name="SQLCommand">所执行的SQL命令</param>
        /// <param name="parameters">参数集合</param>
        /// <returns></returns>
        public static DataSet ExecuteSQLDataSet(string SQLCommand)
        {
            using (OleDbConnection connection = new OleDbConnection(GetConnectionString()))
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }
                OleDbDataAdapter adapter = new OleDbDataAdapter(SQLCommand, connection);
                DataSet ds = new DataSet();
                adapter.Fill(ds, "ds");
                connection.Close();
                return ds;
            }
        }

        /// <summary>
        /// 执行SQL语句并返回包含查询结果的DataReader
        /// </summary>
        /// <param name="SQLCommand">所执行的SQL命令</param>
        /// <param name="parameters">参数集合</param>
        /// <returns></returns>
        public static OleDbDataReader ExecuteSQLDataReader(string SQLCommand, OleDbParameter[] parameters)
        {
            using (OleDbConnection connection = new OleDbConnection(GetConnectionString()))
            {
                OleDbCommand command = new OleDbCommand(SQLCommand, connection);
                for (int i = 0; i < parameters.Length; i++)
                    command.Parameters.Add(parameters[i]);
                if (connection.State == ConnectionState.Closed)
                    connection.Open();
                OleDbDataReader dataReader = command.ExecuteReader();
                connection.Close();
                return dataReader;
            }
        }
    }
}